<?php

App::uses('AppModel', 'Model');

/**
 * ModuleSmsBroadcastList Model
 *
 * @property ModuleSmsAccount $ModuleSmsAccount
 * @property ModuleSmsAccountBroadcast $ModuleSmsAccountMessage
 * @property ModuleSmsBroadcastListContact $ModuleSmsBroadcastListContact
 */
class ModuleSmsBroadcastList extends AppModel {

    /**
     * Use database config
     *
     * @var string
     */
    public $useDbConfig = 'organisations';

    /**
     * Display field
     *
     * @var string
     */
    public $displayField = 'name';


    //The Associations below have been created with all possible keys, those that are not needed can be removed

    /**
     * belongsTo associations
     *
     * @var array
     */
    public $belongsTo = array(
        'ModuleSmsAccount' => array(
            'className' => 'ModuleSmsAccount',
            'foreignKey' => 'module_sms_account_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
    );

    /**
     * hasMany associations
     *
     * @var array
     */
    public $hasMany = array(
        'ModuleSmsAccountBroadcast' => array(
            'className' => 'ModuleSmsAccountBroadcast',
            'foreignKey' => 'module_sms_broadcast_list_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        ),
        'ModuleSmsBroadcastListContact' => array(
            'className' => 'ModuleSmsBroadcastListContact',
            'foreignKey' => 'module_sms_broadcast_list_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        ),
        'ModuleSmsBroadcastListFilter' => array(
            'className' => 'ModuleSmsBroadcastListFilter',
            'foreignKey' => 'module_sms_broadcast_list_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        )
    );
    
    public function getList($account_id, $broadcast_list_id) {
        $return = $this->find('first', array(
            'contain' => array(
                'ModuleSmsBroadcastListFilter' => array(
                    'conditions' => array(
                        'ModuleSmsBroadcastListFilter.active' => 1
                    )
                )
            ),
            'conditions' => array(
                'ModuleSmsBroadcastList.module_sms_account_id' => $account_id,
                'ModuleSmsBroadcastList.id' => $broadcast_list_id
            )
        ));
        
        if( $return ) {
            
            $response = array(
                'id' => $return['ModuleSmsBroadcastList']['id'],
                'name' => $return['ModuleSmsBroadcastList']['name'],
                'size' => $return['ModuleSmsBroadcastList']['size'],
                'type' => $return['ModuleSmsBroadcastList']['type'],
                'sender_id' => $return['ModuleSmsBroadcastList']['sender_id'],
                'filters' => array()
            );
            
            foreach($return['ModuleSmsBroadcastListFilter'] as $filter)  {
                $response['filters'][] = $filter;
            }
            
            return $response;
        }
        
        return false;
    }

    public function buildList($account_id, $broadcast_list_id, $limit = null, $offset = null) {
        
        $clauses = $this->prepareClauses($account_id, $broadcast_list_id);
        $limit_clause = "";
        
        if( $offset == null && $limit > 0 ) {
            $limit_clause = "LIMIT $limit ";
        }
        
        else if( $offset > 0 && $limit > 0 ) {
            $limit_clause = "LIMIT $offset, $limit ";
        }
        
        $query = "SELECT Member.id, Member.first_name, Member.last_name, Member.dob, Member.occupation, Member.gender,
                    concat(Member.last_name, ' ', Member.first_name) as Member__full_name,
                    FLOOR(DATEDIFF(NOW(), dob) / 365.25) as Member__age,
                    MemberPhoneNumber.type as number_type, MemberPhoneNumber.number "
                . "FROM `members` Member "
                . "INNER JOIN `member_phone_numbers` MemberPhoneNumber ON MemberPhoneNumber.member_id = Member.id AND MemberPhoneNumber.active = 1 AND MemberPhoneNumber.type = 'mobile' AND MemberPhoneNumber.number IS NOT NULL AND MemberPhoneNumber.number != '' "
                . "LEFT JOIN `member_emails` MemberEmail ON MemberEmail.member_id = Member.id AND MemberEmail.active = 1 "
                . "INNER JOIN `organisation_members` OrganisationMember ON OrganisationMember.member_id = Member.id AND OrganisationMember.active = 1 AND OrganisationMember.approved = 1 "
                . ($clauses['where'] ? "WHERE {$clauses['where']} " : '')
                . ($clauses['having'] ? "HAVING {$clauses['having']} " : '')
                . "ORDER BY concat(Member.last_name, ' ', Member.first_name) "
                . $limit_clause;

        $result = ClassRegistry::init('Member')->query($query);
        //$result['query'] = $query;
        
        if( $result ) {
            $list = $this->getList($account_id, $broadcast_list_id);
            $response = array();

            foreach($result as $res) {
                $response[] = array_merge($res['Member'], $res['MemberPhoneNumber']);
            }
            
            return array('status' => 'success', 'list' => $list, 'contacts' => $response);
        }
        
        else {
            return array('status' => 'failed', 'message' => 'No contacts found', 'query' => $query);
        }
    }
    
    public function buildPredefinedList($account_id, $category_id, $limit = null, $offset = null) {
        
        //$clauses = $this->prepareClauses($account_id, $category_id);
        $account = $this->ModuleSmsAccount->find('first', array(
            'fields' => array('organisation_id', 'sender_id'),
            'conditions' => array(
                'ModuleSmsAccount.id' => $account_id
            )
        ));
        
        $organisation_id = $account['ModuleSmsAccount']['organisation_id'];
        $category = null; $total_members = 0;
        
        if( $category_id != -1) {
            $category = ClassRegistry::init('OrganisationMemberCategory')->getCategory($organisation_id, $category_id);
        } else {
            $total_members = ClassRegistry::init('OrganisationMember')->getTotalMembers($organisation_id);
        }
        
        $limit_clause = "";
        
        if( $offset == null && $limit > 0 ) {
            $limit_clause = "LIMIT $limit ";
        }
        
        else if( $offset > 0 && $limit > 0 ) {
            $limit_clause = "LIMIT $offset, $limit ";
        }
        
        $conditions = array(
            "OrganisationMember.organisation_id = " . $organisation_id
        );
        
        if( $category_id > 0 ) {
            $conditions[] = "OrganisationMember.organisation_member_category_id = {$category_id}";
        }
        
        $where_clauses = implode(" AND ", $conditions);
        
        $query = "SELECT Member.id, Member.first_name, Member.last_name, Member.dob, Member.occupation, Member.gender,
                    concat(Member.last_name, ' ', Member.first_name) as Member__full_name,
                    FLOOR(DATEDIFF(NOW(), dob) / 365.25) as Member__age,
                    MemberPhoneNumber.type as number_type, MemberPhoneNumber.number "
                . "FROM `members` Member "
                . "INNER JOIN `member_phone_numbers` MemberPhoneNumber ON MemberPhoneNumber.member_id = Member.id AND MemberPhoneNumber.active = 1 AND MemberPhoneNumber.type = 'mobile' AND MemberPhoneNumber.number IS NOT NULL AND MemberPhoneNumber.number != '' "
                //. "LEFT JOIN `member_emails` MemberEmail ON MemberEmail.member_id = Member.id AND MemberEmail.active = 1 "
                . "INNER JOIN `organisation_members` OrganisationMember ON OrganisationMember.member_id = Member.id AND OrganisationMember.active = 1 AND OrganisationMember.approved = 1 "
                . "WHERE {$where_clauses}  "
                . "ORDER BY concat(Member.last_name, ' ', Member.first_name) "
                . $limit_clause;

        $result = ClassRegistry::init('Member')->query($query);
        
        if( $result ) {
            $list = array(
                'id' => $category ? $category['category']['id'] : '-1',
                'name' => $category ? $category['category']['name'] : 'All Members',
                'size' => $category ? $category['category']['member_count'] : $total_members,
                'type' => 'predefined',
                'sender_id' => $account['ModuleSmsAccount']['sender_id'],
                'filters' => array()
            );
            
            $response = array();

            foreach($result as $res) {
                $response[] = array_merge($res['Member'], $res['MemberPhoneNumber']);
            }
            
            return array('status' => 'success', 'list' => $list, 'contacts' => $response);
        }
        
        else {
            return array('status' => 'failed', 'message' => 'No contacts found', 'query' => $query);
        }
    }
    
    public function getListSize($account_id, $broadcast_list_id) {
        $clauses = $this->prepareClauses($account_id, $broadcast_list_id);
        
        $query = "SELECT count(*) as cnt FROM (" 
                    . "SELECT Member.id,
                        concat(Member.last_name, ' ', Member.first_name) as Member__full_name,
                        FLOOR(DATEDIFF(NOW(), dob) / 365.25) as Member__age "
                    . "FROM `members` Member "
                    . "INNER JOIN `member_phone_numbers` MemberPhoneNumber ON MemberPhoneNumber.member_id = Member.id AND MemberPhoneNumber.active = 1 AND MemberPhoneNumber.number IS NOT NULL AND MemberPhoneNumber.number != '' AND MemberPhoneNumber.type = 'mobile' "
                    //. "LEFT JOIN `member_emails` MemberEmail ON MemberEmail.member_id = Member.id AND MemberEmail.active = 1 "
                    . "INNER JOIN `organisation_members` OrganisationMember ON OrganisationMember.member_id = Member.id AND OrganisationMember.active = 1 AND OrganisationMember.approved = 1 "
                    . ($clauses['where'] ? "WHERE {$clauses['where']} " : '')
                    . ($clauses['having'] ? "HAVING {$clauses['having']} " : '')
                . ") AS data";

        $result = ClassRegistry::init('OrganisationMember')->query($query);
        
        $count = $result[0][0]['cnt'];
        
        return array('status' => 'success', 'size' => $count);
    }
    
    private function prepareClauses($account_id, $broadcast_list_id) {
        $account = $this->ModuleSmsAccount->find('first', array(
            'conditions' => array('ModuleSmsAccount.id' => $account_id),
            'recursive' => -1
        ));
        
        $list = $this->find('first', array(
            'contain' => array(
                'ModuleSmsBroadcastListFilter' => array(
                    'conditions' => array(
                        'ModuleSmsBroadcastListFilter.active' => 1
                    ),
                    'order' => array('ModuleSmsBroadcastListFilter.optional' => 'desc')
                )
            ),
            'conditions' => array(
                'ModuleSmsBroadcastList.id' => $broadcast_list_id
            )
        ));
        
        $having_clauses = ''; $where_clauses = '';
        
        if( $list ) {
            $required_conditions = array(
                'OrganisationMember.organisation_id = '. $account['ModuleSmsAccount']['organisation_id']
            );
            
            $optional_conditions = array();
            
            $having = array();
            
            foreach($list['ModuleSmsBroadcastListFilter'] as $filter) {
                $query = ''; $is_having = false;
                
                if( strstr($filter['field'], 'group_type_id') != false ) {
                    continue;
                }
                
                switch($filter['condition']) {
                    case '=':
                    case '>=':
                    case '>':
                    case '<':
                    case '<=':
                        $value = addslashes($filter['value']);
                        $query = "{$filter['field']} {$filter['condition']} '$value' ";
                        break;
                    case '%{s}':
                    case '{s}%':
                    case '%{s}%':
                        $value = str_replace('{s}', addslashes($filter['value']), $filter['condition']);
                        $query = $filter['field'] . " LIKE '$value' ";
                        break;
                }
                
                if( $query != '' ) {
                    if( ($filter['field'] && strpos($filter['field'], '.') == false) || $is_having == true  ) {
                        $having[] = $query;
                    } else {
                        if( $filter['optional'] == 0 ) {
                            $required_conditions[] = $query;
                        }
                        
                        else {
                            $optional_conditions[] = $query;
                        }
                    }
                }
            }
            
            $group_sql = "SELECT    O.id 
                        FROM      `organisation_members` O  
                        JOIN      (
                            SELECT count(*) as matches, OrganisationMemberGroup.organisation_member_id 
                            FROM `organisation_member_groups` OrganisationMemberGroup
                            INNER JOIN `organisation_groups` OrganisationGroup ON OrganisationGroup.id = OrganisationMemberGroup.organisation_group_id
                            WHERE 
                                %filters%
                            GROUP BY OrganisationMemberGroup.organisation_member_id 
                        ) OMG ON (OMG.matches = %filter_count% AND OMG.organisation_member_id = O.id)
                        GROUP BY O.id";
            
            $group_filters = array();
            
            foreach($list['ModuleSmsBroadcastListFilter'] as $filter ) {
                if( strstr($filter['field'], 'group_type_id') != false ) {
                    $group_type_id = str_replace("group_type_id_", "", $filter['field']);
                    $group_id = intval($filter['value']);
                    $clause = "OrganisationGroup.organisation_group_type_id = $group_type_id AND OrganisationMemberGroup.organisation_group_id = $group_id ";
                    
                    $group_filters[] = $clause;
                }
            }
            
            if( $group_filters ) {
                $clauses = implode(" OR ", $group_filters );
                $groups_clause = str_replace(array("%filters%", '%filter_count%'), array($clauses, count($group_filters)), $group_sql);
                $required_conditions[] = " OrganisationMember.id IN ($groups_clause)";
            }
            
            $required_where_clauses = implode(" AND ", $required_conditions);
            $optional_where_cluases = implode(" OR ", $optional_conditions);
            $where_clauses = $optional_where_cluases != '' ? "($required_where_clauses) AND ($optional_where_cluases)" : $required_where_clauses;
            
            $having_clauses = implode(" AND ", $having );
        }
        
        return array('where' => $where_clauses, 'having' => $having_clauses);
    }
}
